package k.dao.base;

import k.dto.BaseDTO;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * BaseDao
 * @author kzhou
 */
@Component
public class BaseDao {
	Log log = LogFactory.getLog(BaseDao.class);
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Autowired
	private JdbcTemplate jdbcTemplate;

    //新增
    public <T extends BaseDTO> Long insert(String sql,T t){
        try {
            KeyHolder keyholder = new GeneratedKeyHolder();
            SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(t);
            namedParameterJdbcTemplate.update(sql, namedParameters,keyholder);
            return keyholder.getKey().longValue();
        } catch (Exception e) {
            log.error(e.getMessage());
            return -1L;
        }
    }
	//修改
	public <T extends BaseDTO> int update(String sql,T t){
		try {
            SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(t);
            namedParameterJdbcTemplate.update(sql, namedParameters);
			return 0;
		} catch (Exception e) {
			log.error(e.getMessage());
			return -1;
		}
	}
    //删除
    public int delete(String sql,Object...ids){
        try {
            return jdbcTemplate.update(sql, ids);
        } catch (Exception e) {
            log.error(e.getMessage());
            return -1;
        }
    }
    //新增222
    public <T extends BaseDTO> Integer insertCz(String sql){
        try {
            Integer status = jdbcTemplate.update(sql);
            return status;
        } catch (Exception e) {
            log.error(e.getMessage());
            return -1;
        }
    }
    //批量写
    public <T extends BaseDTO> void batch(final String sql,List<T> t){
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(t.toArray());
        namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    //查询
    public <T extends BaseDTO> List<T> query(String sql, Class<T> beanClass, Object[] params) {
        return jdbcTemplate.query(sql,params,new BeanPropertyRowMapper<T>(beanClass));
    }
    //查询 without param
    public <T extends BaseDTO> List<T> query(String sql, Class<T> beanClass) {
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<T>(beanClass));
    }

    //分页查询
    public <T extends BaseDTO> List<T> queryBySlice(String sql, Class<T> beanClass, Object[] params,int page, int count) {
        if(page <= 0 || count <= 0){ // find all
            return query(sql, beanClass,params);
        }else{
            int from = (page - 1) * count;
            count = (count > 0) ? count : Integer.MAX_VALUE;
            Pattern p = Pattern.compile("order\\s*by.*");
            Matcher m = p.matcher(sql);
            String sqlpart="";
           if(m.find()){
               // sqlpart= sql.substring(sql.lastIndexOf("from"),sql.indexOf(m.group(0)));
               sqlpart="("+ sql.substring(0,sql.indexOf(m.group(0)))+") xx";
            }else{
              // sqlpart=sql.substring(sql.lastIndexOf("from"),sql.length());
               sqlpart="("+ sql.substring(0,sql.length())+") xx";
           }
            String sqlcount="select count(*) total  from ";
            sqlcount=sqlcount+sqlpart;
            List<BaseDTO> totalList=query(sqlcount.toString(), BaseDTO.class, params);

            List<T> result=query(sql + " LIMIT ?,?",beanClass, ArrayUtils.addAll(params, new Integer[]{from, count}));
            for(T t:result){
                t.setTotal(totalList.get(0).getTotal());
            }
            return result;
        }
    }
    //分页查询
    public <T extends BaseDTO> List<T> queryBySlice(String sql, Class<T> beanClass,int page, int count) {
        if(page <= 0 || count <= 0){ // find all
            return query(sql, beanClass);
        }else{
            int from = (page - 1) * count;
            count = (count > 0) ? count : Integer.MAX_VALUE;
            Pattern p = Pattern.compile("order\\s*by.*");
            Matcher m = p.matcher(sql);
            String sqlpart="";
           if(m.find()){
               // sqlpart= sql.substring(sql.lastIndexOf("from"),sql.indexOf(m.group(0)));
               sqlpart="("+ sql.substring(0,sql.indexOf(m.group(0)))+") xx";
            }else{
              // sqlpart=sql.substring(sql.lastIndexOf("from"),sql.length());
               sqlpart="("+ sql.substring(0,sql.length())+") xx";
           }
            String sqlcount="select count(*) total  from ";
            sqlcount=sqlcount+sqlpart;
            List<BaseDTO> totalList=query(sqlcount.toString(), BaseDTO.class);

            List<T> result=query(sql + " LIMIT ?,?",beanClass, ArrayUtils.addAll(new Integer[]{from, count}));
            for(T t:result){
                t.setTotal(totalList.get(0).getTotal());
            }
            return result;
        }
    }

    //查询byID
    public <T extends BaseDTO> T queryById(String sql, Class<T> beanClass, Object... ids) {
        try {
            return jdbcTemplate.queryForObject(sql,ids,new BeanPropertyRowMapper<T>(beanClass));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    //查询返回Object
    public <T extends Object> T queryForObject(String sql, Class<T> beanClass, Object... ids) {
        try {
            return jdbcTemplate.queryForObject(sql,ids,beanClass);
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }


    //校验值是否存在
    public int chkIsExist(String table,String col,String colVal){
        final StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) as c from ");
        sql.append(table);
        sql.append(" where ").append(col).append(" = ").append("'" + colVal + "'");
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }


    //取值列表
    public String getKVVal(String type){
        final String sql = "select group_concat(val) from sys_val_list where val_type = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{type}, String.class);
    }
    //取值列表
    public String getKVVals(String sql){
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    //取值列表id
    public String getKVId(String type,String val){
        final String sql = "select group_concat(val_list_id) from sys_val_list where val_type = ? and val=?";
        return jdbcTemplate.queryForObject(sql, new Object[]{type,val}, String.class);
    }

    //取值列表
    public String getKVVal(String tableName ,String colName){
        final String sql = "select group_concat(value) from kv where tableName=? and colname=? order by no";
        return jdbcTemplate.queryForObject(sql, new Object[]{tableName,colName}, String.class);
    }

    //取值列表id
    public String getKVKey(String tableName ,String colName){
        final String sql = "select group_concat(key) from kv where tableName=? and colname=? order by no";
        return jdbcTemplate.queryForObject(sql, new Object[]{tableName,colName}, String.class);
    }


    //以键值对的形式返回Map
    public List getKV(String tableName ,String colName){
        final String sql = "select kv.key,value from kv where tableName=? and colname=? order by no";
        return jdbcTemplate.queryForList(sql, new Object[]{tableName, colName});
    }

    /**
     * 通过字段查询id
     */
    public String getIdByName(String table,String col,String colVal,String id){
        String t_id = "";
        try {
            final StringBuilder sql = new StringBuilder();
            sql.append(" select "+id+" from ");
            sql.append(table);
            sql.append(" where ").append(col).append(" = ").append("'" + colVal + "'");
            System.out.println(sql.toString());
            Map<String,Object> temp = jdbcTemplate.queryForMap(sql.toString());
//            for(Map.Entry<String, Object> entry: temp.entrySet()) {
//                System.out.println(entry.getKey() + ":" + entry.getValue());
//            }
            if(temp.size()>0){
                t_id = temp.get(id).toString();
            }
            return t_id;
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }
}
